import sys

import pandas as pd
import re
import tkinter as tk
from tkinter import filedialog

def on_button_handle():
    label.config(text="处理文件中")
    file_path = filedialog.askopenfilename()
    print(file_path)
    handle_file(file_path)
    label.config(text="文件处理完毕")


def handle_file(file_path):
    # 读取 Excel 文件
    # file_path = r"现场库2024.10.21渡口河-七里北地面建设收发存台账日报.xlsx"
    file_path = file_path
    table = pd.read_excel(file_path, index_col=0)

    # 处理表格数据
    table["model"] = table.apply(solve_combined_model, axis=1)
    table['type'] = table.apply(solve_combined_product, axis=1)
    table['DN'] = table.apply(solve_combined_dn, axis=1)
    table["sample"] = table.apply(lambda row: sample(row) if (row["model"] != "未知型号") else "不抽样", axis=1)

    # 检查每个 model 下的 sample 列，如果全为 "不抽样"，则按新的逻辑修改为 "抽样"
    for model_name in table["model"].unique():
        if model_name != "未知型号":
            model_data = table[table["model"] == model_name]
            if (model_data["sample"] == "不抽样").all():
                table.loc[table["model"] == model_name, "sample"] = model_data.apply(sample_if_all_non_sampled, axis=1)

    # 设置所有类型为“仪表”的行的 sample 列为 "抽样"
    table.loc[table["type"] == "仪表", "sample"] = "抽样"

    # 设置所有类型为“紧固件”的行的 sample 列为 "抽样"
    table.loc[table["type"] == "紧固件", "sample"] = "抽样"

    # 其他类别暂时未定义
    ## 设置所有类型为“其他”的行的 sample 列为 "抽样"
    # table.loc[table["type"] == "其他", "sample"] = "抽样"

    # 按1. model 2. 是否抽样 排序
    sorted_table = table.sort_values(by=["model", "sample"], ascending=[True, True])

    # 保存结果
    sorted_table.to_excel("sample_result.xlsx", index=False)
    print("文件处理完毕")


# 定义关键词和型号，使用\b确保完全匹配，并避免316L被匹配为316
pipe_fittings_keywords = r"弯头|三通|四通|大小接头|弯管"
flange_keywords = r"法兰盖|带颈法兰"
instrumentation_keywords = r"压力表"
fasteners_keywords = r"螺栓|螺柱"
# 暂未定义其他类关键词
# other_keywords = r"压力表|螺栓|螺柱"

# 按优先顺序列出型号，使用\b确保完全匹配
models = r"\b(Q245R|Q345E|L245NS|L245N|L360N|L360QS|16MnD|16Mn|CF415K|LF485K2|A105|A105M|A350|A350M|S30408|S30403|SF304L|304L|304|S31608|S31603|SF316L|SF316|316L|316|20#|20g|20G|20R)\b"



# 定义一个函数来判断内容是否包含指定型号（包含物料描述和辅助物料描述）
def solve_combined_model(row):
    content = str(row["物料描述"]) + " " + str(row["辅助物料描述"])
    model_pattern = re.compile(models)
    match = re.search(model_pattern, content)
    return match.group(0) if match else "未知型号"


# 定义一个函数来判断产品类型
def solve_combined_product(row):
    content = str(row["物料描述"]) + " " + str(row["辅助物料描述"])
    if re.search(pipe_fittings_keywords, content):
        return "管件"
    elif re.search(flange_keywords, content):
        return "法兰"
    elif re.search(instrumentation_keywords, content):
        return "仪表"
    elif re.search(fasteners_keywords, content):
        return "紧固件"

    # 暂未定义其他类别
    # elif re.search(other_keywords, content):
    #    return "其他"

    else:
        return "未知"


# 解析 DN 值的函数
dn_pattern = r'DN(\d+)'


# 函数用于从物料描述和辅助物料描述中找到所有匹配的 DN 参数并去重
def solve_combined_dn(row):
    text = str(row["物料描述"]) + " " + str(row["辅助物料描述"])
    matches = re.findall(dn_pattern, text)
    return sorted(set(int(match) for match in matches)) if matches else []


# 判断是否抽样的初始逻辑
def sample(row):
    if row["type"] == "管件":
        if "弯头" in flange_keywords:
            if all(80 <= dn <= 300 for dn in row["DN"]):
                return "抽样"
        if "三通" in flange_keywords:
            if all(80 <= dn <= 300 for dn in row["DN"]):
                return "抽样"
        if "四通" in flange_keywords:
            if all(50 <= dn <= 250 for dn in row["DN"]):
                return "抽样"
        if "弯管" in flange_keywords:
            if all(50 <= dn <= 200 for dn in row["DN"]):
                return "抽样"
        if "大小接头" in flange_keywords:
            if all(80 <= dn <= 400 for dn in row["DN"]):
                return "抽样"

    if row["type"] == "法兰":
        if "带颈法兰" in flange_keywords:
            if all(80 <= dn <= 250 for dn in row["DN"]):
                return "抽样"
        elif "法兰盖" in flange_keywords:
            if all(150 <= dn <= 300 for dn in row["DN"]):
                return "抽样"
        else:
            return "抽样"

    if row["type"] == "仪表":
        return "抽样"

    if row["type"] == "紧固件":
        if "螺栓" in flange_keywords:
            return "抽样"
        if "螺柱" in flange_keywords:
            return "抽样"

    # 其他类别暂时未定义
    '''
    if row["type"] == "其他":
        return "抽样"
    '''
    return "不抽样"



# 定义新的抽样逻辑作为函数
def sample_if_all_non_sampled(row):
    dn_values = row["DN"]
    if not dn_values:
        return "不抽样"

    # 判断管件的抽样规则
    if row["type"] == "管件":
        if "弯头" in flange_keywords:
            if all(0 <= dn <= 300 for dn in row["DN"]):
                return "抽样"
        if "三通" in flange_keywords:
            if all(0 <= dn <= 300 for dn in row["DN"]):
                return "抽样"
        if "四通" in flange_keywords:
            if all(0 <= dn <= 250 for dn in row["DN"]):
                return "抽样"
        if "弯管" in flange_keywords:
            if all(0 <= dn <= 200 for dn in row["DN"]):
                return "抽样"
        if "大小接头" in flange_keywords:
            if all(0 <= dn <= 400 for dn in row["DN"]):
                return "抽样"
        else:
            return "抽样"

    # 判断法兰的抽样规则
    if row["type"] == "法兰":
        if "带颈法兰" in flange_keywords:
            if all(0 <= dn <= 250 for dn in dn_values):
                return "抽样"
        elif "法兰盖" in flange_keywords:
            if all(0 <= dn <= 300 for dn in dn_values):
                return "抽样"
        else:
            return "抽样"

    # 默认返回不抽样
    return "不抽样"

def on_button_exit():
    sys.exit()



if __name__=='__main__':
    # 创建主窗口
    root = tk.Tk()
    root.title("文件处理")
    root.geometry("300x200")

    # 创建标签
    label = tk.Label(root, text="欢迎使用！")
    label.pack(pady=20)


    # 创建按钮
    button = tk.Button(root, text="选择文件", command=on_button_handle)
    button.pack(pady=10)

    # 创建按钮
    button_exit = tk.Button(root, text="退出", command=on_button_exit)
    button_exit.pack(pady=10)

    # 进入主事件循环
    root.mainloop()

